package cn.itcast.anli.dao.impl;

import cn.itcast.anli.dao.EmployeeDAO;
import cn.itcast.anli.domain.Employee;
import cn.itcast.anli.utils.JDBCUtil;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

public class EmployeeDAOImpl implements EmployeeDAO {
    JdbcTemplate template = new JdbcTemplate(JDBCUtil.getDataSource());


    @Override
    public void add(Employee employee) {
        //定义sql语句
        String sql = "insert into emp values(null,?,?,?,?,?,?,?)";
        //执行sql语句
        template.update(sql, employee.getEname(), employee.getJob_id(), employee.getMgr(), employee.getJoindate(), employee.getSalary(), employee.getBonus(), employee.getDept_id());
    }

    @Override
    public void delete(int id) {
        //定义sql语句
        String sql = "delete from emp where id = ?";
        //执行sql语句
        template.update(sql, id);

    }

    @Override
    public Employee findById(int id) {
        //定义sql语句
        String sql = "select * from emp where id = ?";
        //执行sql语句
        Employee employee = template.queryForObject(sql, new BeanPropertyRowMapper<Employee>(Employee.class), id);
        return employee;
    }

    @Override
    public void modify(Employee employee) {
        //定义sql语句
        String sql = "update emp set ename = ?, job_id = ?,mgr = ?,joindate = ?,salary = ?,bonus = ?,dept_id = ? where id = ?";
        //执行sql语句
        template.update(sql, employee.getEname(), employee.getJob_id(), employee.getMgr(), employee.getJoindate(), employee.getSalary(), employee.getBonus(), employee.getDept_id(), employee.getId());

    }

    @Override
    public int totalCount(Map<String, String[]> map) {
        //定义sql语句
        String sql = "select count(*) from emp where 1 = 1";

        Set<String> keySet = map.keySet();
        List<Object> list = new ArrayList<>();
        for (String key : keySet) {
            if ("currentPage".equals(key) || "row".equals(key)) {
                continue;
            }
            String value = map.get(key)[0];
            if (value != null && !value.equals("")) {
                sql += " and " + key + " like ?";
                list.add("%" + value + "%");
            }
        }

        //执行sql语句
        Integer totalCount = template.queryForObject(sql, Integer.class, list.toArray());
        return totalCount;
    }



    @Override
    public List<Employee> pageList(int currentPage, int row, Map<String, String[]> map) {
        int start = (currentPage - 1) * row;
        //定义sql语句
        String sql1 = "";
        Set<String> keySet = map.keySet();
        List<Object> list = new ArrayList<>();
        for (String s : keySet) {
            if ("currentPage".equals(s) || "row".equals(s)) {
                continue;
            }
            String value = map.get(s)[0];
            if (value != null && !value.equals("")) {
                sql1 += " and " + s + " like ?";
                list.add("%" + value + "%");
            }
        }
        String sql = "select * from emp where 1 = 1 " + sql1 + " limit ?,?";
        list.add(start);
        list.add(row);

        //执行sql语句
        List<Employee> query = template.query(sql, new BeanPropertyRowMapper<Employee>(Employee.class), list.toArray());
        return query;
    }
}
